﻿if exists (select * from dbo.sysobjects where name = 'tg_Update_Level_Rating')	drop trigger [dbo].[tg_Update_Level_Rating]Go CREATE TRIGGER dbo.tg_Update_Level_Rating on [Rating]
WITH ENCRYPTION
after insert, update
as
	declare @Id int
	declare @i_Level nvarchar(255), @d_Level nvarchar(255), @Level nvarchar(255), @temp_treecode nvarchar(255)
	declare @Order int
	Select @Id=Id from inserted
	Select @i_Level=Level from inserted
	Select @d_Level=Level from deleted
	if @d_Level<>''
		Select @d_Level=Level from deleted
	else
		select @d_Level='100'
	Select @Order=Order from inserted
	if len(@i_Level)>5
		Select @Level= left(Level,len(Level)-5) + right('000000000'+ cast(@Order as varchar(5)),5) from inserted
	else
		Select @Level=right('000000000'+ cast(@Order as varchar(5)),5) from inserted
	update [Rating] set Order=cast(right(Level,5) as int)
	if exists(select Order from [Rating] where len(Level)=len(@i_Level) and Id<>@Id and Order in (select Order from inserted))
		BEGIN
			if len(@i_Level)>5
				BEGIN
					update [Rating] set Level=left(Level,len(@i_Level)-5) + right('000000000'+ cast(cast(substring(Level,(len(@i_Level)-5)+1,5) as int)+1 as varchar(5)),5) + substring(Level,len(@i_Level)+1,100), Order=Order+1
					where cast(substring(Level,(len(@i_Level)-5)+1,5) as int)>=cast(@Order as varchar(5)) and left(Level,len(@i_Level)-5)=left(@i_Level,len(@i_Level)-5)
				END
			else
				BEGIN
					update [Rating] set Level=right('000000000'+ cast(cast(substring(Level,(len(@i_Level)-5)+1,5) as int)+1 as varchar(5)),5) + substring(Level,len(@i_Level)+1,100), Order=Order+1
					where cast(substring(Level,(len(@i_Level)-5)+1,5) as int)>= cast(@Order as varchar(5))
				END
			select @temp_treecode=Level from [Rating] where Id= cast(@Id as varchar(5))
			update [Rating] set Level=@Level where Id= cast(@Id as varchar(5))
			if @d_Level<>'100'
				BEGIN
					update [Rating] set Level=@Level + substring(Level,len(@temp_treecode)+1,100) where left(Level,len(@temp_treecode))= @temp_treecode
				END
			update [Rating] set Order=cast(right(Level,5) as int)
		END
	else
		BEGIN
			update [Rating] set Level=@Level where Id= cast(@Id as varchar(5))
			if @d_Level<>'100'
				BEGIN
					update [Rating] set Level=@Level + substring(Level,len(@d_Level)+1,100) where left(Level,len(@d_Level))= @d_Level
				END
			update [Rating] set Order=cast(right(Level,5) as int)
		END
Go 